﻿using System;
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Globalization;

namespace TarreOA.TarreOADAL
{
    #region 委托
    /// <summary>
    /// 数据库操作命令委托
    /// </summary>
    /// <param name="command">操作命令</param>
    /// <returns>委托的命令</returns>
    public delegate object CommandDelegate(DbCommand dbcommand);
    /// <summary>
    /// DbDataReader命令委托
    /// </summary>
    /// <param name="dbDataReader">DbDataReader</param>
    public delegate void DBDataReaderDelegate(DbDataReader dbDataReader);
    #endregion

    /// <summary>
    ///数据库操作的辅助类
    /// </summary>
    public class DbHelper
    {
        #region 字段
        private static DbHelper instance;
        private ConnectionStringSettings connectionStringSettings = null;
        private DbProviderFactory dbProviderFactory = null;
         
        /// <summary>
        /// 保存数据库链接字符串
        /// </summary>
        public static string DataConnenctString = "dataSource";
        #endregion

        #region 构造函数
        public DbHelper()
        {
            connectionStringSettings = ConfigurationManager.ConnectionStrings[DataConnenctString];
            if (connectionStringSettings != null)
            {
                if (!string.IsNullOrEmpty(connectionStringSettings.ProviderName) && !string.IsNullOrEmpty(connectionStringSettings.ConnectionString))
                    dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
            }
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="DbConnectSettingName">连接字符串名称</param>
        public DbHelper(string dbConnectSettingName)
        {
            if (string.IsNullOrEmpty(dbConnectSettingName))
            {
                dbConnectSettingName = DataConnenctString;
            }
            connectionStringSettings = ConfigurationManager.ConnectionStrings[dbConnectSettingName];
            if (connectionStringSettings != null)
            {
                if (!string.IsNullOrEmpty(connectionStringSettings.ProviderName) && !string.IsNullOrEmpty(connectionStringSettings.ConnectionString))
                    dbProviderFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);
            }
        }
        #endregion
        #region 类属性
        public static DbHelper Instance
        {
            get
            {
                if (instance == null)
                    instance = new DbHelper("");
                return instance;
            }
        }
        #endregion
        #region 类实例属性
        public string ConnectString
        {
            get { return connectionStringSettings.ConnectionString; }
        }
        public DbProviderFactory ProviderFactory
        {
            get { return dbProviderFactory; }
        }
        public string ProviderString
        {
            get { return connectionStringSettings.ProviderName; }
        }
        #endregion

        /// <summary>
        /// 创建一个数据库连接
        /// </summary>
        /// <returns></returns>
        public DbConnection CreateConnection()
        {
            if (dbProviderFactory == null)
            {
                return null;
            }
            else
            {
                DbConnection dbConnection = dbProviderFactory.CreateConnection();
                dbConnection.ConnectionString = connectionStringSettings.ConnectionString;
                return dbConnection;
            }
        }
        /// <summary>
        /// 执行无结果集Sql
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">Sql语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteNonQuery(CommandType commandType, string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                try
                {
                    return cmd.ExecuteNonQuery();
                }
                catch
                {
                    return -1;
                }
            };
            return (int)ExecuteCmdCallback(commandType, sql, cd);
        }
        /// <summary>
        /// 执行带参数的非查询语句
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="para">参数集合</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteNonQuery(CommandType commandType, string sql, DbParameter[] para)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                return cmd.ExecuteNonQuery();
            };
            return (int)ExecuteCmdCallback(commandType, sql, cd, para);
        }
        /// <summary>
        ///  执行带参数与委托命令的查询语句，并返回相关的委托命令
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">Sql语句</param>
        /// <param name="commandDelegate">委托类型</param>
        /// <param name="para">参数集合</param>
        /// <returns>委托的命令</returns>
        private object ExecuteCmdCallback(CommandType commandType, string sql, CommandDelegate commandDelegate, DbParameter[] para)
        {
            using (DbConnection dbCnn = CreateConnection())
            {
                using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.CommandType = commandType;
                    cmd.CommandText = sql;
                    cmd.Connection = dbCnn;
                    for (int i = 0; i < para.GetLength(0); i++)
                    {
                        cmd.Parameters.Add(para[i]);
                    }
                    dbCnn.Open();
                    return commandDelegate(cmd);
                }
            }
        }
        /// <summary>
        /// 通过DbDataReader来读取数据
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="readdelegate">DBDataReaderDelegate</param>
        public bool ReadData(string sql, DBDataReaderDelegate readdelegate)
        {
            bool result = false;
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                using (DbDataReader dbReader = cmd.ExecuteReader())
                {
                    readdelegate(dbReader);
                    return true;
                }
            };
            result = (bool)ExecuteCmdCallback(CommandType.Text, sql, cd);
            return result;
        }
        private object ExecuteCmdCallback(CommandType commandType, string sql, CommandDelegate commandDelegate)
        {
            using (DbConnection dbCon = CreateConnection())
            {
                using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.CommandType = commandType;
                    cmd.CommandText = sql;
                    cmd.Connection = dbCon;
                    dbCon.Open();
                    return commandDelegate(cmd);
                }
            }
        }
        private object ExecuteCmdCallback(CommandType commandType, CommandDelegate commandDelegate)
        {
            using (DbConnection dbCon = CreateConnection())
            {
                using (DbCommand cmd = dbProviderFactory.CreateCommand())
                {
                    cmd.Connection = dbCon;
                    cmd.CommandType = commandType;
                    dbCon.Open();
                    return commandDelegate(cmd);
                }
            }
        }
        /// <summary>
        /// 根据Sql创建一个DataTable结果集
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">Sql语句</param>
        /// <returns>DataTable</returns>
        public DataTable CreateDataTable(CommandType commandType, string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                using (DbDataReader dr = cmd.ExecuteReader())
                {
                    DataTable dt = new DataTable();
                    dt.Locale = CultureInfo.InvariantCulture;
                    dt.Load(dr);
                    return dt;
                }
            };
            return (DataTable)ExecuteCmdCallback(CommandType.Text, sql, cd);
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略。
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns>结果集中第一行的第一列</returns>
        public string GetValue(string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                return cmd.ExecuteScalar();
            };
            object value = ExecuteCmdCallback(CommandType.Text, sql, cd);
            if (value == null)
                return "";
            return value.ToString();
        }
        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略。
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns>结果集中第一行的第一列</returns>
        public Object GetObject(string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                return cmd.ExecuteScalar();
            };
            return ExecuteCmdCallback(CommandType.Text, sql, cd);
        }
        /// <summary>
        /// 根据Sql语句创建一个DataSet类型的结果集
        /// </summary>
        /// <param name="commandType">命令类型</param>
        /// <param name="sql">Sql语句</param>
        /// <returns>相关数据集</returns>
        public DataSet CreateDataSet(CommandType commandType, string sql)
        {
            CommandDelegate cd = delegate(DbCommand cmd)
            {
                using (DbDataAdapter da = dbProviderFactory.CreateDataAdapter())
                {
                    DataSet ds = new DataSet();
                    ds.Locale = CultureInfo.InvariantCulture;
                    da.SelectCommand = cmd;
                    da.Fill(ds);
                    return ds;
                }
            };
            return (DataSet)ExecuteCmdCallback(CommandType.Text, sql, cd);
        }
    }
}

